import datetime
import json

import cx_Oracle

"""查询物料系统推送订单在入库单查不到的问题"""

user = "pigpos"
passwd = "P1g#2023pos"
listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
# listener = '10.240.24.135/NMDCFARM'
# listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
conn = cx_Oracle.connect(user, passwd, listener)
cursor = conn.cursor()
print_sql_flag = 0


def select(sql) -> json:
    if print_sql_flag == 1:
        print(sql)
    rows = []
    cursor.execute(sql)
    result = cursor.fetchall()
    col_name = cursor.description
    for row in result:
        d = {}
        for col in range(len(col_name)):
            key = col_name[col][0]
            value = row[col]
            if isinstance(value, datetime.datetime):
                value = value.strftime('%Y-%m-%d %H:%M:%S')
            d[key] = value
        rows.append(d)
    js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
    return js


# 关闭连接，释放资源
def disconnect():
    cursor.close()
    conn.close()
    print("关闭数据库连接")


def find_tax_vendor(tax_no, org_code):
    sql = "select mvt.tax_no,listagg(mvt.vendor_code,',') within group (order by mvt.vendor_code) VENDORS " \
          "from MAS_VENDOR_TAX MVT, MAS_VENDOR_ORG MVO where MVT.TAX_NO = '{}' and MVO.PURCHASE_ORG_CODE = '{}' " \
          "and MVT.VENDOR_CODE = MVO.VENDOR_CODE group by mvt.tax_no".format(tax_no, org_code)
    list = select(sql)
    list = json.loads(list)
    if len(list) > 0:
        a = list[0]
        return a['VENDORS']
    return ''


def find_problem_1(company, sub_operation, org_code):
    sql = " select MVT.TAX_NO TAX_NO,count( MVT.VENDOR_CODE) num from MAS_VENDOR_TAX MVT, MAS_VENDOR_ORG MVO where  1=1 " \
          "and MVO.PURCHASE_ORG_CODE = '{}' and MVT.VENDOR_CODE = MVO.VENDOR_CODE and mvt.tax_no in " \
          "( select t.cv_code from center_purchase.INTERFACE_PO_HDR_PC t " \
          "where t.sub_operation = '{}' and t.company = '{}' ) group by MVT.TAX_NO having count( MVT.VENDOR_CODE) > 1".format(
        org_code, sub_operation, company)
    list = select(sql)
    list = json.loads(list)
    if len(list) > 0:
        print('一个税号对应多个供应商(会导致存储报错，解决方案：在ss系统维护正确)：')
        for d in list:
            tax_no = d['TAX_NO']
            vendors = find_tax_vendor(tax_no, org_code)
            print('\t' + '税号[{}]->供应商[{}]'.format(tax_no, vendors))


def find_purchase(org_code, document_no):
    sql = "select * from fr_trn_purchase_header t where t.org_code = '{}' and t.ref_document_no in ('{}')".format(
        org_code, document_no
    )
    list = select(sql)
    list = json.loads(list)
    if len(list) > 0:
        print('单据已入库：')
        print('\t' + document_no)


def find_po_farm_org(org_code, document_no):
    sql = "select d.farm_org from FR_TRN_PO_DETAIL d where d.org_code = '{}' and d.po_document_no = '{}' " \
          "and not exists (select o.farm_org from fr_farm_org o where o.org_code = d.org_code and o.farm_org = d.farm_org)" \
          " ".format(org_code, document_no)
    list = select(sql)
    list = json.loads(list)
    if len(list) > 0:
        print('采购订单里的农场数据不存在：')
        for d in list:
            farm_org = d['FARM_ORG']
            print('\t' + farm_org)


def find_doc_number(org_code, document_no):
    sql = "select h.doc_number from CENTER_PURCHASE.INTERFACE_PO_HDR_PC h where h.company||h.sub_operation = '{}' and h.doc_number = '{}' " \
          " union all " \
          " select h.doc_number from CENTER_PURCHASE.INTERFACE_PO_HDR_PC_AUDIT h where h.company||h.sub_operation = '{}' and h.doc_number = '{}' " \
          " union all " \
          " select ph.po_document_no as doc_number from fr_trn_po_header ph where ph.org_code = '{}' and ph.po_document_no = '{}'" \
          "".format(org_code, document_no, org_code, document_no, org_code, document_no)
    list = select(sql)
    list = json.loads(list)
    if len(list) == 0:
        print('单据未发送到云上农牧：')
        print('\t' + document_no)


def find_problem(company, sub_operation, org_code, stock_type, document_no):
    # 一个税号对应多个供应商
    find_problem_1(company, sub_operation, org_code)
    # 单据已入库
    find_purchase(org_code, document_no)
    # 采购订单里的农场数据不存在
    find_po_farm_org(org_code, document_no)
    # 检查单据是否发送到云上农牧
    find_doc_number(org_code, document_no)


if __name__ == '__main__':
    print('开始查询')
    stock_type = '20'
    company = '0426'
    sub_operation = '22111'
    org_code = company + sub_operation
    document_no = 'E289230831B0038'
    # 是否打印sql
    print_sql_flag = 1
    try:
        r = find_problem(company, sub_operation, org_code, stock_type, document_no)

    finally:
        disconnect()
